package dao;

import entity.JobInfo;
import openConn.DruidUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class JobInfoDao {
    public static int add(JobInfo jobInfo , Connection connection) throws SQLException {
        String  sql = "insert into job_info(job_id,job_name,job_city,company,salary,experience,ed,city_code,job) values (?,?,?,?,?,?,?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,jobInfo.getJobId());
        statement.setString(2,jobInfo.getJonName());
        statement.setString(3,jobInfo.getJobCity());
        statement.setString(4,jobInfo.getCompany());
        statement.setString(5,jobInfo.getSalary());
        statement.setString(6,jobInfo.getExperience());
        statement.setString(7,jobInfo.getEd());
        statement.setString(8,jobInfo.getCityCode());
        statement.setString(9,jobInfo.getJob());

        int count = statement.executeUpdate();
        statement.close();
        return count;
    }


    public List<JobInfo> selectByPage(int pageNum, int pageSize, String job, String cityCode) throws SQLException {
        List<JobInfo> javaList = new ArrayList<>();
        Connection connection = DruidUtils.openConn();
        String sql = "call select_by_page(?,?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,pageNum);
        statement.setInt(2,pageSize);
        statement.setString(3,job);
        statement.setString(4,cityCode);
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
            String jobId = resultSet.getString("job_id");
            String jobName = resultSet.getString("job_name");
            String jobCity = resultSet.getString("job_city");
            String jobCompany = resultSet.getString("company");
            String jobSalary = resultSet.getString("salary");
            String jobExperience = resultSet.getString("experience");
            String Edu = resultSet.getString("ed");
            String cityCode1 = resultSet.getString("city_code");
            String job1 = resultSet.getString("job");
            JobInfo jobInfo = new JobInfo(jobId,jobName,jobCity,jobCompany,jobSalary,jobExperience,Edu,cityCode1,job1);
            javaList.add(jobInfo);
        }
        statement.close();
        connection.close();
        return javaList;
    }
    public List<JobInfo> selectByCity(int pageNum, int pageSize, String cityCode) throws SQLException {

        List<JobInfo> javaList = new ArrayList<>();
        Connection connection = DruidUtils.openConn();
        String sql = "call select_by_city(?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,pageNum);
        statement.setInt(2,pageSize);
        statement.setString(3,cityCode);
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
            String jobId = resultSet.getString("job_id");
            String jobName = resultSet.getString("job_name");
            String jobCity = resultSet.getString("job_city");
            String jobCompany = resultSet.getString("company");
            String jobSalary = resultSet.getString("salary");
            String jobExperience = resultSet.getString("experience");
            String Edu = resultSet.getString("ed");
            String cityCode1 = resultSet.getString("city_code");
            String job1 = resultSet.getString("job");
            JobInfo jobInfo = new JobInfo(jobId,jobName,jobCity,jobCompany,jobSalary,jobExperience,Edu,cityCode1,job1);
            javaList.add(jobInfo);
        }
        statement.close();
        connection.close();
        return javaList;
    }
    public List<JobInfo> selectByJob(int pageNum, int pageSize, String job) throws SQLException {

        List<JobInfo> javaList = new ArrayList<>();
        Connection connection = DruidUtils.openConn();
        String sql = "call select_by_job(?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,pageNum);
        statement.setInt(2,pageSize);
        statement.setString(3,job);
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
            String jobId = resultSet.getString("job_id");
            String jobName = resultSet.getString("job_name");
            String jobCity = resultSet.getString("job_city");
            String jobCompany = resultSet.getString("company");
            String jobSalary = resultSet.getString("salary");
            String jobExperience = resultSet.getString("experience");
            String Edu = resultSet.getString("ed");
            String cityCode1 = resultSet.getString("city_code");
            String job1 = resultSet.getString("job");
            JobInfo jobInfo = new JobInfo(jobId,jobName,jobCity,jobCompany,jobSalary,jobExperience,Edu,cityCode1,job1);
            javaList.add(jobInfo);
        }
        statement.close();
        connection.close();
        return javaList;
    }
    public List<JobInfo> selectAll(int pageNum, int pageSize) throws SQLException {
        List<JobInfo> javaList = new ArrayList<>();
        Connection connection = DruidUtils.openConn();
        String sql = "call select_all(?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,pageNum);
        statement.setInt(2,pageSize);

        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
            String jobId = resultSet.getString("job_id");
            String jobName = resultSet.getString("job_name");
            String jobCity = resultSet.getString("job_city");
            String jobCompany = resultSet.getString("company");
            String jobSalary = resultSet.getString("salary");
            String jobExperience = resultSet.getString("experience");
            String Edu = resultSet.getString("ed");
            String cityCode1 = resultSet.getString("city_code");
            String job1 = resultSet.getString("job");
            JobInfo jobInfo = new JobInfo(jobId,jobName,jobCity,jobCompany,jobSalary,jobExperience,Edu,cityCode1,job1);
            javaList.add(jobInfo);
        }
        statement.close();
        connection.close();
        return javaList;
    }

    public int selectCount(String cityCode,String jobName) throws SQLException {
        int count = 0;
        Connection connection = DruidUtils.openConn();
        String sql = "select count(*) as allcount from job_info where city_code = ? And job = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,cityCode);
        statement.setString(2,jobName);
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
            count = resultSet.getInt("allcount");
        }
        statement.close();
        connection.close();
        return count;
    }
    public int selectCountByJob(String jobName) throws SQLException {
        int count = 0;
        Connection connection = DruidUtils.openConn();
        String sql = "select count(*) as allcount from job_info where job = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,jobName);
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
            count = resultSet.getInt("allcount");
        }
        statement.close();
        connection.close();
        return count;

    }
    public int selectCountByCity(String cityCode) throws SQLException {
        int count = 0;
        Connection connection = DruidUtils.openConn();
        String sql = "select count(*) as allcount from job_info where city_code = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,cityCode);
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
            count = resultSet.getInt("allcount");
        }
        statement.close();
        connection.close();
        return count;

    }
    public int selectCountAll() throws SQLException {
        int count = 0;
        Connection connection = DruidUtils.openConn();
        String sql = "select count(*) as allcount from job_info";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
            count = resultSet.getInt("allcount");
        }
        statement.close();
        connection.close();
        return count;
    }
}
